package com.central.apps.controller;

import com.central.app.service.HandlerService;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.exception.service.ServiceException;
import com.central.common.util.SysUserUtil;
import com.central.common.web.Result;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @Author mengweitao
 * 前方数据初始化导入    人员，组织架构。
 */
@Log4j2
@RestController
@RequestMapping("/UserDeptImport")
public class UserDeptImportController {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @Autowired
    private HandlerService handlerService;
    @Autowired
    private PasswordEncoder passwordEncoder;

    /**
     * 机构导入
     *
     * @throws IOException
     */
    @PostMapping("/deptImport")
    @Transactional(rollbackFor = Exception.class)
    public Result deptImport(@RequestParam("file") MultipartFile myfile) throws Exception {
        try {
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                //poixls会报错
                workbook = new HSSFWorkbook(myfile.getInputStream());
//                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                return Result.failed("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("导入模板");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                return Result.failed("请填写数据");
            }

            for (int i = 0; i <= rows; i++) {
                Row row = sheet1.getRow(i);
                if (row != null) {
                    String code = getCellValue(row, 0).toString().trim();//机构编码
                    String fullname = getCellValue(row, 1).toString().trim();//机构名称
                    String pcode = getCellValue(row, 2).toString().trim();//上级机构编码
                    String parent = getCellValue(row, 3).toString().trim();//上级机构名称
                    String depttype = getCellValue(row, 4).toString().trim();//机构类型1总公司2分公司3子公司4部门5其他
                    //判断模板标头是否正确
                    if (i == 0) {
                        if (!"机构编码".equals(code) || !"机构名称".equals(fullname) || !"上级机构编码".equals(pcode)
                                || !"上级机构名称".equals(parent) || !"机构类型".equals(depttype)) {
                            return Result.failed("导入的模板错误，请下载模板进行导入");
                        }
                    } else {
                        //校验必填项
                        if (StringUtils.isBlank(code) || StringUtils.isBlank(fullname)) {
                            int line = i + 1;
                            return Result.failed("第" + line + "行有必填项未填，请填写后重新导入！");
                        }

                        String depttypeNew = "";
                        switch (depttype) {
                            case "总公司":
                                depttypeNew = "1";
                                break;
                            case "分公司":
                                depttypeNew = "2";
                                break;
                            case "子公司":
                                depttypeNew = "3";
                                break;
                            case "部门":
                                depttypeNew = "4";
                                break;
                            case "其他":
                                depttypeNew = "5";
                                break;
                            default:
                                break;
                        }

                        LoginAppUser loginAppUser = SysUserUtil.getLoginAppUser();
                        String worknumber = loginAppUser.getWorknumber();


                        //TODO 校验机构编码不能重复
                        int line = i + 1;
                        List<Map<String, Object>> bySql = handlerService.findBySql("SELECT * from sys_dept where `code`='" + code + "'");
                        if(CollectionUtils.isNotEmpty(bySql)){
                            throw new ServiceException("第"+line+"行的机构编码:"+code+"已存在，修改后重新导入！");
                        }

                        handlerService.findBySql("insert into sys_dept(depttype,code,fullname,pcode,parent,createuser,status,del_flag) values ('" +
                                depttypeNew + "','" + code + "','" + fullname + "','" + pcode + "','" + parent + "','" + worknumber + "','" + "0" + "','" + "0" + "')");
                    }
                }
            }
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            Map<String, Object> map = new HashMap<>();
            map.put("code", 1);
            map.put("data", "");
            map.put("message", e.getMessage());
            return Result.failed(map, e.getMessage());
        }
        return Result.succeed("导入成功");
    }

    /**
     * 人员，用户导入
     *
     * @param myfile
     * @return
     * @throws Exception
     */
    @PostMapping("/userPersonImport")
    @Transactional(rollbackFor = Exception.class)
    public Result userPersonImport(@RequestParam("file") MultipartFile myfile) throws Exception {
        try {
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                //poixls会报错
                workbook = new HSSFWorkbook(myfile.getInputStream());
//                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                return Result.failed("文件不是excel文件");
            }

            //获取excel的Sheet页
            Sheet sheet1 = workbook.getSheet("人员导入标准模板");
            //判断excel一共多少行
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                //excel里面没数据
                return Result.failed("请填写数据");
            }

            //人员台账编号
            int personid = 100001;
            for (int i = 0; i <= rows; i++) {
                Row row = sheet1.getRow(i);
                if (row != null) {
                    String username = getCellValue(row, 0).toString().trim();//登录名
                    String password = getCellValue(row, 1).toString().trim();//密码
                    String nickname = getCellValue(row, 2).toString().trim();//姓名
                    String post = getCellValue(row, 3).toString().trim();//职务
                    String worknumber = getCellValue(row, 4).toString().trim();//工号
                    String idnumber = getCellValue(row, 5).toString().trim();//身份证号
                    String phone = getCellValue(row, 6).toString().trim();//手机号
                    String email = getCellValue(row, 7).toString().trim();//电子邮箱
                    String education = getCellValue(row, 8).toString().trim();//学历
                    String sex = getCellValue(row, 9).toString().trim();//性别
                    String deptcode = getCellValue(row, 10).toString().trim();//所属机构
                    String position = getCellValue(row, 11).toString().trim();//现岗位(人员台账)
                    String typework = getCellValue(row, 12).toString().trim();//工种
                    String tips = getCellValue(row, 13).toString().trim();//备注
                    //判断模板标头是否正确
                    if (i == 0) {
                        if (!"登录名".equals(username) || !"密码".equals(password) || !"姓名".equals(nickname)
                                || !"职务".equals(post) || !"工号".equals(worknumber) || !"工号".equals(worknumber)
                                || !"身份证号".equals(idnumber) || !"手机号".equals(phone) || !"电子邮箱".equals(email)
                                || !"学历".equals(education) || !"性别".equals(sex) || !"所属机构".equals(deptcode)
                                || !"现岗位".equals(position) || !"工种".equals(typework) || !"备注".equals(tips)) {
                            return Result.failed("导入的模板错误，请下载模板进行导入");
                        }
                    } else {
                        int line = i + 1;
                        //校验必填项
                        if (StringUtils.isBlank(username) || StringUtils.isBlank(password) || StringUtils.isBlank(nickname) || StringUtils.isBlank(worknumber) || StringUtils.isBlank(deptcode)) {
                            return Result.failed("第" + line + "行有必填项未填，请填写后重新导入！");
                        }

                        //登陆人 导入人（admin）
                        LoginAppUser loginAppUser = SysUserUtil.getLoginAppUser();
                        String nickname1 = loginAppUser.getNickname();
                        Long userId = loginAppUser.getId();

                        //根据机构编码查机构名
                        String fullname = "";
                        List<Map<String, Object>> bySql1 = handlerService.findBySql("SELECT fullname from sys_dept where `code`='" + deptcode + "'");
                        if(CollectionUtils.isNotEmpty(bySql1)){
                            fullname = MapUtils.getString(bySql1.get(0),"fullname");
                        }

                        //往人员台账放数据
                        if(i ==1){
                            List<Map<String, Object>> bySql = handlerService.findBySql("select personid from gq_person order by personid desc limit 1");
                            if(CollectionUtils.isNotEmpty(bySql)){
                                personid = (MapUtils.getIntValue(bySql.get(0), "personid")+1);
                            }
                        }else{
                            personid = personid+1;
                        }


                        handlerService.findBySql("insert into gq_person(phone,typework,version,createuserdesc,name,status,email,position" +
                                        ",createuser,deptcode,delflag,deptname1,personid,isnonew) values ('"+
                                        phone+"','"+typework+"','"+"1"+"','"+nickname1+"','"+nickname+"','"+"活动"+"','"+email+"','"+position+"','"+
                                        userId+"','"+deptcode+"','"+"0"+"','"+fullname+"','"+personid+"','"+"否"+"')");


                        String gqPersonId = "";
                        List<Map<String, Object>> bySql2 = handlerService.findBySql("select id from gq_person where personid='" + personid + "'");
                        if(CollectionUtils.isNotEmpty(bySql2)){
                            gqPersonId = MapUtils.getString(bySql2.get(0), "id");
                        }


                        //校验用户名不能重复
                        List<Map<String, Object>> bySql3 = handlerService.findBySql("select * from sys_user where username = '" + username + "'");
                        if(CollectionUtils.isNotEmpty(bySql3)){
                            return Result.failed("第" + line + "行用户名："+username+"已存在，修改后重新导入！");
                        }

                        //处理密码
                        String encodePassword = passwordEncoder.encode(password);
                        String sexNew = "";
                        switch (sex) {
                            case "女":
                                sexNew = "0";
                                break;
                            case "男":
                                sexNew = "1";
                                break;
                            default:
                                break;
                        }
                        if(StringUtils.isBlank(sex)){
                            sexNew = "1";
                        }
                        //放系统用户放数据
                        handlerService.findBySql("insert into sys_user(username,password,nickname,worknumber,idnumber,phone,email,sex,enabled,type,education," +
                                "post,createuser,islogin,ispermit,del_flag,deptname,deptcode,weiinfo,firstlogin,leaveoffice,errorLoginTimes,islock,field2,field3,field10) values ('"+
                                        username+"','"+encodePassword+"','"+nickname+"','"+personid+"','"+idnumber+"','"+phone+"','"+email+"','"+
                                        sexNew+"','"+"1"+"','"+"APP"+"','"+education+"','"+post+"','"+userId+"','"+"1"+"','"+"1"+"','"+"0"+"','"+fullname+"','"+
                                        deptcode+"','"+password+"','"+"1"+"','"+"0"+"','"+"0"+"','"+"0"+"','"+personid+"','"+nickname+"','"+gqPersonId+"')");

                    }
                }
            }
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            Map<String, Object> map = new HashMap<>();
            map.put("code", 1);
            map.put("data", "");
            map.put("message", e.getMessage());
            return Result.failed(map, "导入失败");
        }
        return Result.succeed("导入成功");
    }


    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }
}
